﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TPCN.Commons;

namespace TPCN.DAL
{
    public class HoaDonBanDAL
    {
        public static HoaDonBanObj GetByID(string HoadonbanID)
        {
            HoaDonBanObj obj = new HoaDonBanObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoaDonBan_GetByID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HoadonbanID", HoadonbanID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        obj = obj.SetValuesFromDataReader(dr);
                    }
                    dr.Close();
                }
            }
            return obj;
        }

        public static DataTable Loinhuan(DateTime startDate, DateTime endDate)
        {
            DataTable result = new DataTable();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_TinhLoiNhuan";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                result = Database.db.GetData(cmd);
            }
            return result;
        }

        public static List<HoaDonBanObj> GetByAll()
        {
            List<HoaDonBanObj> result = new List<HoaDonBanObj>();
            HoaDonBanObj obj = new HoaDonBanObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoaDonBan_GetByAll";
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HoaDonBanObj> GetByDate(DateTime startDate, DateTime endDate)
        {
            List<HoaDonBanObj> result = new List<HoaDonBanObj>();
            HoaDonBanObj obj = new HoaDonBanObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoadonban_GetByDate";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<HoaDonBanObj> GetByDateAndCustomerId(DateTime startDate, DateTime endDate, String customerId)
        {
            List<HoaDonBanObj> result = new List<HoaDonBanObj>();
            HoaDonBanObj obj = new HoaDonBanObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoadonban_GetByDateAndCustomer";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@startDate", startDate);
                cmd.Parameters.AddWithValue("@endDate", endDate);
                cmd.Parameters.AddWithValue("@customerID", customerId);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static DataTable GetByDateAndCustomer(DateTime startDate, DateTime endDate, String customerId)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoadonban_TinhDoanhThuTheoKhachHang";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@start", startDate);
                cmd.Parameters.AddWithValue("@end", endDate);
                cmd.Parameters.AddWithValue("@customerID", customerId);
                return Database.db.GetData(cmd);
            }
        }

        public static string GetMoneyByOrderID(string HoadonbanID)
        {
            string str = String.Empty;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbHoadonban_GetMoneyByOrderID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HoadonbanID", HoadonbanID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    dr.Read();
                    str = dr.GetDouble(0).ToString();
                    dr.Close();
                }
            }
            return str;
        }

        public static double GetRealMoneyOfOrder(string HoadonbanID)
        {
            string money = GetMoneyByOrderID(HoadonbanID);
            double mMoney = Double.Parse(money);
            double discount = 0;
            HoaDonBanObj hdb = new HoaDonBanObj();
            hdb = HoaDonBanDAL.GetByID(HoadonbanID);
            discount = Double.Parse(hdb.Chietkhau);
            if (hdb.Hinhthucchietkhau == Const.KEY_DISCOUNT_TYPE_MONEY)
                mMoney -= discount;
            else
                mMoney -= discount * mMoney;
            return mMoney;
        }

        public static bool Insert(HoaDonBanObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHoaDonBan_Insert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Ngaylap", (obj.Ngaylap == null) ? String.Empty : obj.Ngaylap);
                    cmd.Parameters.AddWithValue("@KhachID", (obj.KhachID == null) ? String.Empty : obj.KhachID);
                    cmd.Parameters.AddWithValue("@Chietkhau", (obj.Chietkhau == null) ? "0" : obj.Chietkhau);
                    cmd.Parameters.AddWithValue("@Hinhthucchietkhau", (obj.Hinhthucchietkhau == null) ? "0" : obj.Hinhthucchietkhau);
                    cmd.Parameters.AddWithValue("@Noidungchietkhau", (obj.Noidungchietkhau == null) ? String.Empty : obj.Noidungchietkhau);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool Update(HoaDonBanObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbHoaDonBan_Update";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@HoadonbanID", (obj.HoadonbanID == null) ? String.Empty : obj.HoadonbanID);
                    cmd.Parameters.AddWithValue("@Ngaylap", (obj.Ngaylap == null) ? String.Empty : obj.Ngaylap);
                    cmd.Parameters.AddWithValue("@KhachID", (obj.KhachID == null) ? String.Empty : obj.KhachID);
                    cmd.Parameters.AddWithValue("@Chietkhau", (obj.Chietkhau == null) ? "0" : obj.Chietkhau);
                    cmd.Parameters.AddWithValue("@Hinhthucchietkhau", (obj.Hinhthucchietkhau == null) ? "0" : obj.Hinhthucchietkhau);
                    cmd.Parameters.AddWithValue("@Noidungchietkhau", (obj.Noidungchietkhau == null) ? String.Empty : obj.Noidungchietkhau);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool Delete(string HoadonbanID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_tbHoaDonBan_DeleteByID";
                    cmd.Parameters.AddWithValue("@HoadonbanID", HoadonbanID);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
